<?php
class Core_Model_Db_Connection {
	
	protected $_conn = null;

	protected $_option;

	public function __construct(array $option) {
		$this->_option = $option;
	}

	protected function _connect() {
		if($this->_conn) return;

		$option = $this->_option;
		$host     = $option['host'];
		$database = $option['database'];
		$username = $option['username'];
		$password = $option['password'];
		try {
			$conn = new PDO("mysql:host=$host;dbname=$database", $username, $password);
			$conn->setAttribute(PDO::ATTR_ERRMODE, PDO::ERRMODE_EXCEPTION);
			$this->_conn = $conn;
		} catch(PDOException $e) {
			throw $e;
		}
	}

	public function getTable($name) {
		return $this->_option['prefix'] . $name;
	}

	public function beginTransaction() {
		$this->_connect();
		$this->_conn->beginTransaction();
	}

	public function commit() {
		$this->_conn->commit();
	}

	public function rollback() {
		$this->_conn->rollBack();
	}

	public function query($sql, $bind=array()) {
		$this->_connect();
		$stmt = $this->_conn->prepare($sql);
		$stmt->setFetchMode(PDO::FETCH_ASSOC);
		$stmt->execute($bind);
		return $stmt;
	}

	/**
	 * Inserts a table row with specified data.
	 *
	 * @param mixed $table The table to insert data into.
	 * @param array $bind Column-value pairs.
	 * @return int The number of affected rows.
	 */
	public function insert($table, array $bind) {
		// prepare cols and bind value
		$cols = array();
		$vals = array();
		foreach($bind as $col => $val) {
			$cols[] = $col;
			$vals[] = ':'.$col;
		}
		
		// build the statement
		$sql = "INSERT INTO "
			 . $table
			 . '(' . implode(', ', $cols) . ') '
			 . 'VALUES(' . implode(', ', $vals) . ')';

		 // execute the statement and return the number of affected rows
		$stmt = $this->query($sql, $bind);
		$result = $stmt->rowCount();
		return $result;
	}

	public function update($table, array $bind, $where='') {
		// Build "col = ?" pairs for the statement,
		$set = array();
		foreach($bind as $col => $val) {
			$set[] = $col . ' = :' . $col;
		}
		
		// build the statement
		$sql = "UPDATE "
			 . $table
			 . ' SET ' . implode(', ', $set)
			 . (($where) ? " WHERE $where" : '');

		 // execute the statement and return the number of affected rows
		$stmt = $this->query($sql, $bind);
		$result = $stmt->rowCount();
		return $result;
	}

	/**
	 * Deletes table rows based on a WHERE clause.
	 *
	 * @param  mixed        $table The table to update.
	 * @param  mixed        $where DELETE WHERE clause(s).
	 * @return int          The number of affected rows.
	 */
	public function delete($table, $where='') {
		// nuild the DELETE statement
		$sql = "DELETE FROM "
			 . $table
			 . (($where) ? " WHERE $where" : '');

		// execute the statement and return the number of affected rows
		$stmt = $this->query($sql);
		$result = $stmt->rowCount();
		return $result;
	}

	public function fetchAll($sql, $bind=array()) {
		return $this->query($sql, $bind)->fetchAll();
	}

	public function fetchRow($sql, $bind=array()) {
		return $this->query($sql, $bind)->fetch();
	}

	public function fetchCol($sql, $bind=array()) {
		return $this->query($sql, $bind)->fetchAll(PDO::FETCH_COLUMN);
	}

	public function fetchPairs($sql, $bind=array()) {
		$stmt = $this->query($sql, $bind);
		$data = array();
		while($row = $stmt->fetch(PDO::FETCH_NUM)) {
			$data[$row[0]] = $row[1];
		}
		return $data;
	}

	public function fetchOne($sql, $bind=array()) {
		return $this->query($sql, $bind)->fetchColumn();
	}

	public function test() {
		$result = true;
		try {
			$this->_connect();
		} catch(PDOException $e) {
			$result = false;
		}
		return $result;
	}
}